﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;

namespace ORM.Test
{
    public class SqlBulider
    {
        //单表查询
        public string GetQuery<TEntity>(Expression<Func<TEntity, bool>> exp,out List<SqlParameter> sqlParameters)
        {
            StringBuilder sb = new StringBuilder();
            Type type = typeof(TEntity);
            sb.Append($"SELECT " +
                $"{string.Join(",", type.GetProperties().Select(prop => prop.GetColName()))} " +
                $"FROM  {type.GetTableName()} ");
            VCustomExpressionVisitor VcExression = new VCustomExpressionVisitor();
            VcExression.Visit(exp) ;
            ExpressionResualtModel ExpressMsg=VcExression.GetResualt();
            if (ExpressMsg.EntityASName.Count>0)
            {
                sb.Append($" AS {ExpressMsg.EntityASName[ExpressMsg.EntityASName.Count - 1]} WHERE 1=1 ");
            }
            if (ExpressMsg.Sql!=null)
            {
                sb.Append("AND "+ExpressMsg.Sql);
            }
            sb.Append(";");
            sqlParameters = new List<SqlParameter>();
            sqlParameters = ExpressMsg.Paramters;
            return sb.ToString();
        }
        /// <summary>
        /// 参数化并且添加
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="model"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public string GetInsert<TEntity>(TEntity model, out List<SqlParameter> sqlParameters)
        {

            StringBuilder sb = new StringBuilder();
            Type type = typeof(TEntity);
            sb.Append($"INSERT INTO  {type.GetTableName()} (" +
                $"{string.Join(",", type.NotKey().Where(t=>t.GetValue(model)!=null).Select(prop => prop.GetColName()))}) " +
                $"Values  ({string.Join(",",type.NotKey().Select(t=>"@"+t.GetColName()))}) ");

            sqlParameters = new List<SqlParameter>();
            foreach (var item in type.NotKey())
            {
                sqlParameters.Add(new SqlParameter("@"+item.GetColName(),item.GetValue(model)));
            }
            sb.Append(";");
            //返回添加的ID
            return sb.ToString();
        }

        public string GetUpdate<TEntity>(TEntity model, out List<SqlParameter> sqlParameters)
        {

            StringBuilder sb = new StringBuilder();
            Type type = typeof(TEntity);
            PropertyInfo PropKey = type.GetKey();
            sb.Append($"Update   {type.GetTableName()} Set " +
                $"{string.Join(",", type.NotKey().Where(t => t.GetValue(model) != null).Select(prop => prop.GetColName()+"=@"+prop.GetColName()))} " +
                $" Where  {PropKey.GetColName()}=@{PropKey.GetColName()} ");

            sqlParameters = new List<SqlParameter>();
            foreach (var item in type.GetProperties().Where(t=>t.GetValue(model)!=null))
            {
                sqlParameters.Add(new SqlParameter("@" + item.GetColName(), item.GetValue(model)));
            }
            //返回添加的ID
            sb.Append(";");
            return sb.ToString();
        }

        public string GetDelete<TEntity>(Expression<Func<TEntity,bool>> exp, out List<SqlParameter> sqlParameters)
        {

            StringBuilder sb = new StringBuilder();
            Type type = typeof(TEntity);
            PropertyInfo PropKey = type.GetKey();
            sb.Append($"Delete  FROM {type.GetTableName()} " +
                $"  ");

            VCustomExpressionVisitor VcExression = new VCustomExpressionVisitor();
            VcExression.Visit(exp);
            ExpressionResualtModel ExpressMsg = VcExression.GetResualt();
            sqlParameters= ExpressMsg.Paramters;
            if (ExpressMsg.Sql.Length>0)
            {
                sb.Append(" AS "+ExpressMsg.EntityASName[ExpressMsg.EntityASName.Count-1]);
                sb.Append(" Where 1=1 ");
            }
            sb.Append($" AND {ExpressMsg.Sql};");

            //返回添加的ID
            return sb.ToString();
        }
        /// <summary>
        /// T3是返回的类型
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <typeparam name="T3"></typeparam>
        /// <returns></returns>
        public string LeftJoin<T1,T2>(Expression<Func<T1,T2,bool>> LeftExp,out List<SqlParameter> _Listparamter)
        {
            StringBuilder sb = new StringBuilder();
            VCustomExpressionVisitor VcExression = new VCustomExpressionVisitor();
            VcExression.Visit(LeftExp);
            ExpressionResualtModel ExpressMsg = VcExression.GetResualt();
            string ASName = LeftExp.ToString();
            Console.WriteLine(ASName[4]);
            Type type = typeof(T2);
            sb.Append($" Left JOIN {type.GetTableName()} AS {ASName[4]} on {ExpressMsg.Sql}");
            _Listparamter = ExpressMsg.Paramters;
            return sb.ToString();
        }

        public string ReturnModel<T,T1,T2,T3>(string [] str)
        {
            Type type = typeof(T);
            StringBuilder sb = new StringBuilder();
            //
           
            sb.Append($"SELECT ");
            Type type1 = typeof(T1);
            Type type2 = typeof(T2);
            Type type3 = typeof(T3);
            foreach (var item in type.GetProperties())
            {
                List<PropertyInfo> props=type1.GetProperties().Where(t=>t.GetColName()==item.GetColName()).ToList();
                if (props!=null && props.Count>0)
                {
                    sb.Append(str[1][1]+"."+props[0].GetColName()+",");
                    continue;
                }
                props = type2.GetProperties().Where(t => t.GetColName() == item.GetColName()).ToList();
                if (props != null && props.Count > 0)
                {
                    sb.Append(str[1][2] +"."+ props[0].GetColName()+",");
                    continue;
                }
                props = type3.GetProperties().Where(t => t.GetColName() == item.GetColName()).ToList();
                if (props != null && props.Count > 0)
                {
                    sb.Append(str[1][3]+"."+ props[0].GetColName()+",");
                    continue;
                }
            }
            sb.Remove(sb.ToString().Length-1,1);
            sb.Append($" FROM {str[0]} AS {str[1][1]}");
            
            return sb.ToString();
        }
        public string GetInsertID()
        {
            return "SELECT CAST(scope_identity() AS int);";
        }
       
    }
}
